<?php

/**
 * @filesource Communicates with the Contact control file to know which student
 * data to get and set. It is the only file that has the SQL queries and MySQL
 * connections
 *
 * @copyright 2011 by Dan, Steven, Brice, Mike, and Aaron.  This program is part
 * of Internship 2 Career, which is free software. It comes with absolutely no
 * warranty. You can redistribute or modify it under the terms of the GNU
 * General Public License as published by the Free Software Foundation (For
 * more information, see <http://www.gnu.org/licenses/).
 *
 * @version March 6, 2011
 *
 * @author Aaron Jarzombek and Brice Rader
 */
/*
 * This is the database file.
 * It contacts the control file to know
 * which internship orrertunity data to get and set.
 * It is the only file that has the SQL queries and MySQL connections
 *
 */

class dbOpportunity {

   static function createTable() {
      //Create table opportunity
      $query = "
            create table opportunity(
                opportunityID           int             NOT NULL AUTO_INCREMENT,
                organizationID		int		NOT NULL,
                contactID		int		NULL,
                title			varchar(100)	NOT NULL,
                numPositionsAvailable	int		NOT NULL,
                semester		varchar(50)	NOT NULL,
                payRate			varchar(32)	NOT NULL,
                duration		varchar(50)	NOT NULL,
                responsibilities	text		NOT NULL,
                qualifications		text		NULL,
                instructions		text		NULL,
                comments                text            NULL,
                categories              text            NULL,
                pending                 int(1)          NULL,
                dateAdded		timestamp       NOT NULL

                DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                constraint PK_Opportunity PRIMARY KEY (opportunityID),
                constraint FK_Opportunity_Organization FOREIGN KEY
                (organizationID)
                    references organization (organizationID),
                constraint FK_Opportunity_Contact FOREIGN KEY (contactID)
                    references contact (contactID)
                );
      ";
      connectToDatabase();
      $result = mysql_query($query);
      echo mysql_error();
      mysql_close();
      if ($result == false) {
         return false;
      }

      //Previous query was successful.
      return true;
   }

   /**
    * Retrieves all the data in the table Opportunity.
    *
    * @return Resource object that contains retrieved data. If query is fails,
    * it returns false.
    */
   static function getAllOpportunities() {
      $category = $_SESSION['category'];
      $city = $_SESSION['city'];
      $major = $_SESSION['major'];
      $search = $_SESSION['search'];
      $sortBy = $_SESSION['sortBy'];
      $searchFor = $_SESSION['searchFor'];

      $query = dbOpportunity::buildQuery($category, $city, $search, $sortBy, $searchFor);


      connectToDatabase();
      $result = mysql_query($query);
      echo mysql_error();
      mysql_close();

      if (!$result) {
         return false;
      }
      //Query was successful.
      return $result;
   }

   function buildQuery($cat, $city, $search, $sort, $searchFor) {

      $query = "SELECT o.city, o.stateName, o.organizationName, io.* FROM
                opportunity io, ORGANIZATION o
                WHERE io.organizationID = o.organizationID
                AND io.categories LIKE '%$cat%'
                AND o.city LIKE '%$city%'";

      switch ($searchFor) {
         case 'Positions':
            $query .= "AND io.title LIKE '%$search%'";
            break;
         case 'Qualifications':
            $query .= "AND io.qualifications LIKE '%$search%'";
            break;
         case 'Responsibilities':
            $query .= "AND io.responsibilities LIKE '%$search%'";
            break;
         case 'Organizations':
            $query .= "AND o.organizationName LIKE '%$search%'";
            break;
      }

      //need to order by $sort
      switch ($sort) {
         case 'org_asc':
            $query .= "ORDER BY o.organizationName ASC";
            break;
         case 'org_desc':
            $query .= "ORDER BY o.organizationName DESC";
            break;
         case 'city_asc':
            $query .= "ORDER BY o.city ASC";
            break;
         case 'city_desc':
            $query .= "ORDER BY o.city DESC";
            break;
         case 'sem_desc':
            $query .= "ORDER BY io.semester DESC";
            break;
         case 'sem_asc':
            $query .= "ORDER BY io.semester ASC";
            break;
         case 'pos_desc':
            $query .= "ORDER BY io.title DESC";
            break;
         case 'pos_asc':
            $query .= "ORDER BY io.title ASC";
            break;
         case 'numAvailable_asc':
            $query .= "ORDER BY io.numPositionsAvailable ASC";
            break;
         case 'numAvailable_desc':
            $query .= "ORDER BY io.numPositionsAvailable DESC";
            break;
      }

      return $query;
   }

   function repPaid() {

      $category = $_SESSION['category'];
      $query = "SELECT o.city, o.stateName, o.organizationName, io.* FROM
      opportunity io, organization o WHERE o.organizationID = io.organizationID
      AND io.categories LIKE '%$category%' ORDER BY io.payRate DESC";
      connectToDatabase();
      $result = mysql_query($query);
      echo mysql_error();
      mysql_close();
      if ($result == false) {
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
    * Deletes data from the Opportunity table.
    *
    * @param Opportunity $o The data to be deleted. It has values for all the
    * columns in the table, including primary key value.
    *
    * @return ture if the query is successful.
    */
   function deleteFromOpportunity($id) {

      //Compose the DELETE FROM query string.
      $query = sprintf("DELETE FROM opportunity WHERE opportunityID = %d", $id);
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }

      //Query was successful.
      return true;
   }

   /**
    * Updates opportunity information.
    * @param object $o Class Opportunity
    * @return int returns 0 if there was an error updating, else returns 1
    */
   function updateOpportunity($id, $o) {
      $opportunityID = $id;
      $organizationID = sanitizeStr($o->getOrganizationID());
      $contactID = sanitizeStr($o->getContactID());
      $title = sanitizeStr($o->getTitle());
      $numPositionsAvailable = sanitizeStr($o->getNumPositionsAvailable());
      $semester = sanitizeStr($o->getSemester());
      $payrate = sanitizeStr($o->getPayRate());
      $duration = sanitizeStr($o->getDuration());
      $rb = sanitizeStr($o->getResponsibilities());
      $qualif = sanitizeStr($o->getQualifications());
      $instruct = sanitizeStr($o->getInstructions());
      $comments = sanitizeStr($o->getComments());
      $categories = sanitizeStr($o->getCategories());
      $pending = sanitizeStr($o->getPending());
      $dateadded = sanitizeStr($o->getDateAdded());

      connectToDatabase();

      if ($opportunityID == '') {
         return false;
      } else {
         if ($organizationID != '') {
            if (!mysql_query("UPDATE opportunity SET organizationID=
      '$organizationID' WHERE opportunityID=$opportunityID"))
               die("Could not update organizationID with ID =
                        $opportunityID");
         }
         if ($contactID != '') {
            if (!mysql_query("UPDATE opportunity SET contactID='$contactID'
      WHERE opportunityID=$opportunityID"))
               die("Could not update contactID with ID = $opportunityID");
         }else{
           mysql_query("UPDATE opportunity SET contactID=null
      WHERE opportunityID=$opportunityID");
         }
         if ($title != '') {
            if (!mysql_query("UPDATE opportunity SET title='$title' WHERE
      opportunityID=$opportunityID"))
               die("Could not update title with ID = $opportunityID");
         }
         if ($numPositionsAvailable != '') {
            if (!mysql_query("UPDATE opportunity SET numPositionsAvailable=
      '$numPositionsAvailable' WHERE opportunityID=$opportunityID"))
               die("Could not update numPositionAvailable with ID =
                    $opportunityID");
         }
         if ($semester != '') {
            if (!mysql_query("UPDATE opportunity SET semester='$semester'
      WHERE opportunityID=$opportunityID"))
               die("Could not update semester with ID = $opportunityID");
         }
         if ($payrate != '') {
            if (!mysql_query("UPDATE opportunity SET payRate='$payrate'
      WHERE opportunityID=$opportunityID"))
               die("Could not update payRate with ID = $opportunityID");
         }
         if ($duration != '') {
            if (!mysql_query("UPDATE opportunity SET duration='$duration'
      WHERE opportunityID=$opportunityID"))
               die("Could not update duration with ID = $opportunityID");
         }if ($rb != '') {
            if (!mysql_query("UPDATE opportunity SET responsibilities='$rb'
      WHERE opportunityID=$opportunityID"))
               die("Could not update responsibilitys with ID =
                        $opportunityID");
         }
         if ($qualif != '') {
            if (!mysql_query("UPDATE opportunity SET qualifications='$qualif'
      WHERE opportunityID=$opportunityID"))
               die("Could not update qualifications with ID =
                        $opportunityID");
         }
         if ($instruct != '') {
            if (!mysql_query("UPDATE opportunity SET instructions='$instruct'
      WHERE opportunityID=$opportunityID"))
               die("Could not update instructions with ID =
                        $opportunityID");
         }
         if ($comments != '') {
            if (!mysql_query("UPDATE opportunity SET comments='$comments'
      WHERE opportunityID=$opportunityID"))
               die("Could not update comments with ID = $opportunityID");
         }
         if ($categories != '') {
            if (!mysql_query("UPDATE opportunity SET categories='$categories'
      WHERE opportunityID=$opportunityID"))
               die("Could not update categories with ID = $opportunityID");
         }
         if ($pending != '') {
            if (!mysql_query("UPDATE opportunity SET pending='$pending'
      WHERE opportunityID=$opportunityID"))
               die("Could not update pending with ID = $opportunityID");
         }
         if ($dateadded != '') {
            if (!mysql_query("UPDATE opportunity SET dateAdded='$dateadded'
      WHERE opportunityID=$opportunityID"))
               die("Could not update dateAdded with ID = $opportunityID");
         }
         mysql_close();
         return true;
      }
   }

   /**
    * Adds an opportunity to the database.
    * @param object $o Class Opportunity
    * @return bool Returns TRUE if query completes successfully.
    */
   static function addOpportunity($o, &$id = null) {

      if (!$o instanceof Opportunity) {
         echo ("Invalid arguement insertInto ( ) function<br />");
         return false;
      }
      //Compose the INSERT INTO query string. Primary key values is missing.
      //Database server automatically generates a unique ID for the primary
      //key.
      $query = sprintf("INSERT INTO opportunity VALUES(null, %d, %d, '%s', 
            '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, null)", $o->getOrganizationID(), $o->getContactID(), $o->getTitle(), $o->getNumPositionsAvailable(), $o->getSemester(), $o->getPayRate(), $o->getDuration(), $o->getResponsibilities(), $o->getQualifications(), $o->getInstructions(), $o->getComments(), $o->getCategories(), $o->getPending()
      );
      connectToDatabase();
      $result = mysql_query($query);
      $id = mysql_insert_id();
      echo mysql_error();
      mysql_close();
      if ($result == false) {
         return false;
      }

      //Query was successful.
      printf("Opportunity added has id %d\n<br />", mysql_insert_id());
      return true;
   }

   /**
    * Checks to see if the ID exists.
    * @param object $o Class Opportunity
    * @return int Returns FALSE of ID cannot be found, and TRUE if ID is found.
    */
   function checkID($o) {
      if (!($o instanceof Opportunity)) {
         return false;
      }
      $opportunityID = $o->checkOpportunity();
      connectToDatabase();
      if (mysql_num_rows(mysql_query("SELECT * FROM opportunity WHERE
      opportunityID=$opportunityID")) == 0) {
         echo mysql_error();
         mysql_close();
         //nothing exists mysql_num_rows
         return false;
      } else {
         return true;
      }
   }

   /**
    * Gets all the opportunity information for an opportunity with a certain ID
    * @param int $opportunityID ID opportunityID of opportunity you want to get
    * @return object Returns FALSE of the ID ID does not exist, or an
    * Opportunity object if it does.
    */
   function getOpportunity($opportunityID) {
      $o = new Opportunity();
      $result = mysql_query("SELECT * FROM opportunity WHERE opportunityID=
      $opportunityID");
      if (!$result) {
         return false; //id does not exist
      } else {
         $row = mysql_fetch_assoc($result);
         $o->setOpportunityID($opportunityID);
         $o->setOrganizationID($row['organizationID']);
         $o->setContactID($row['contactID']);
         $o->setTitle($row['title']);
         $o->setNumPositionsAvailable($row['numPositionsAvailable']);
         $o->setSemester($row['semester']);
         $o->setPayrate($row['payRate']);
         $o->setDuration($row['duration']);
         $o->setResponsibilities($row['responsibilities']);
         $o->setQualifications($row['qualifications']);
         $o->setInstructions($row['instructions']);
         $o->setComments($row['comments']);
         $o->setCategories($row['categories']);
         $o->setPending($row['pending']);
         $o->setDateAdded($row['dateadded']);
         return $o;
      }
   }

   function getOpportunityID($o) {
      $result = mysql_query("SELECT opportunityID FROM opportunity WHERE
            opportunityID='$o'");
      $row = mysql_fetch_row($result);
      return $row['0'];
   }

   function getOrganizationID($o) {
      $query = "SELECT organizationID FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getContactID($o) {
      $query = "SELECT contactID FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getTitle($o) {
      $query = "SELECT title FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getNumPositionsAvailable($o) {
      $query = "SELECT numPositionsAvailable FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getSemester($o) {
      $query = "SELECT semester FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getPayRate($o) {
      $query = "SELECT payRate FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getDuration($o) {
      $query = "SELECT duration FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getResponsibilities($o) {
      $query = "SELECT responsibilities FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getQualifications($o) {
      $query = "SELECT qualifications FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getInstructions($o) {
      $query = "SELECT instructions FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getComments($o) {
      $query = "SELECT comments FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getCategories($o) {
      $query = "SELECT categories FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getPending($o) {
      $query = "SELECT pending FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getDateAdded($o) {
      $query = "SELECT dateAdded FROM opportunity
        WHERE opportunityID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   public static function getAllByID($id, &$output = null) {
      $query = sprintf("SELECT organizationID, contactID, title,
            numPositionsAvailable,semester, payRate, duration, responsibilities,
            qualifications, instructions, comments, categories, dateAdded FROM
            opportunity WHERE opportunityID=%d", $id);
      //printf("->Query: %s<br />", $query);
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();

      if ($result == false) {
         $output = sprintf("%s", mysql_error());
         return false;
      }
      //process result and print out warning or attribute values
      return dbOpportunity::checkForOneInstance($result, $id, $output);
   }

   private static function checkForOneInstance($result, $id, &$output) {
      //Local variables are initialized.
      $errorStatus = false;

      $howMany = mysql_num_rows($result);
      //printf("->Found %d instance(s) for organizationID=%d.<br />", $howMany, $id);
      switch ($howMany) {
         case 0:
            $output = sprintf("No Opportunity instance with ID=%d was found.", $id);
            $errorStatus = true;
            break;
         case 1:
            //mysql_fetch() DOES NOT return primary key values
            $output = mysql_fetch_assoc($result);
            $errorStatus = false;
            break;
         default:
            $output = sprintf("There are multiple Opportunities instances with
                                  ID=%d.", $id);
            $errorStatus = true;
            break;
      }
      return $errorStatus;
   }

}

?>
